using System;
using System.Text;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using OVS.BusinessObjects;

namespace OVS.DAL
{
	public partial class UserDAO
	{
		public UserDAO()
		{
			DbProviderHelper.GetConnection();
		}
		public List<User> GetUsers()
		{
            try
            {
                List<User> lstUsers = new List<User>();
                DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [user_id],[user_name],[user_tel],[user_mail],[user_role],[user_lvl_notif],[user_pwd],[user_login] FROM [User]", CommandType.Text);
                DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
                while (oDbDataReader.Read())
                {
                    User oUser = new User();
                    oUser.user_id = Convert.ToInt32(oDbDataReader["user_id"]);
                    oUser.user_name = Convert.ToString(oDbDataReader["user_name"]);
                    oUser.user_tel = Convert.ToString(oDbDataReader["user_tel"]);
                    oUser.user_mail = Convert.ToString(oDbDataReader["user_mail"]);

                    if (oDbDataReader["user_role"] != DBNull.Value)
                        oUser.user_role = Convert.ToInt32(oDbDataReader["user_role"]);

                    if (oDbDataReader["user_lvl_notif"] != DBNull.Value)
                        oUser.user_lvl_notif = Convert.ToInt32(oDbDataReader["user_lvl_notif"]);

                    if (oDbDataReader["user_pwd"] != DBNull.Value)
                        oUser.user_pwd = Convert.ToString(oDbDataReader["user_pwd"]);

                    if (oDbDataReader["user_login"] != DBNull.Value)
                        oUser.user_login = Convert.ToString(oDbDataReader["user_login"]);
                    lstUsers.Add(oUser);
                }
                oDbDataReader.Close();
                return lstUsers;
            }
            catch (Exception ex)
            {
                throw ex;
            }            
		}
		public User GetUser(int user_id)
		{
			try
			{
				User oUser = new User();
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("SELECT [user_id],[user_name],[user_tel],[user_mail],[user_role],[user_lvl_notif],[user_pwd],[user_login] FROM [User] WHERE [user_id]=@user_id",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_id",DbType.Int32,user_id));
				DbDataReader oDbDataReader = DbProviderHelper.ExecuteReader(oDbCommand);
				while (oDbDataReader.Read())
				{
					oUser.user_id = Convert.ToInt32(oDbDataReader["user_id"]);
					oUser.user_name = Convert.ToString(oDbDataReader["user_name"]);
					oUser.user_tel = Convert.ToString(oDbDataReader["user_tel"]);
					oUser.user_mail = Convert.ToString(oDbDataReader["user_mail"]);

					if(oDbDataReader["user_role"] != DBNull.Value)
						oUser.user_role = Convert.ToInt32(oDbDataReader["user_role"]);

					if(oDbDataReader["user_lvl_notif"] != DBNull.Value)
						oUser.user_lvl_notif = Convert.ToInt32(oDbDataReader["user_lvl_notif"]);

					if(oDbDataReader["user_pwd"] != DBNull.Value)
						oUser.user_pwd = Convert.ToString(oDbDataReader["user_pwd"]);

					if(oDbDataReader["user_login"] != DBNull.Value)
						oUser.user_login = Convert.ToString(oDbDataReader["user_login"]);
				}
				oDbDataReader.Close();
				return oUser;
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int AddUser(User oUser)
		{
			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("INSERT INTO [User]([user_name],[user_tel],[user_mail],[user_role],[user_lvl_notif],[user_pwd],[user_login])VALUES(@user_name,@user_tel,@user_mail,@user_role,@user_lvl_notif,@user_pwd,@user_login);SELECT SCOPE_IDENTITY();",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_name",DbType.String,oUser.user_name));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_tel",DbType.String,oUser.user_tel));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_mail",DbType.String,oUser.user_mail));
				if (oUser.user_role.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_role",DbType.Int32,oUser.user_role));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_role",DbType.Int32,DBNull.Value));
				if (oUser.user_lvl_notif.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_lvl_notif",DbType.Int32,oUser.user_lvl_notif));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_lvl_notif",DbType.Int32,DBNull.Value));
				if (oUser.user_pwd!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_pwd",DbType.String,oUser.user_pwd));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_pwd",DbType.String,DBNull.Value));
				if (oUser.user_login!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_login",DbType.String,oUser.user_login));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_login",DbType.String,DBNull.Value));

				return Convert.ToInt32(DbProviderHelper.ExecuteScalar(oDbCommand));
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int UpdateUser(User oUser)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("UPDATE [User] SET [user_name]=@user_name,[user_tel]=@user_tel,[user_mail]=@user_mail,[user_role]=@user_role,[user_lvl_notif]=@user_lvl_notif,[user_pwd]=@user_pwd,[user_login]=@user_login WHERE [user_id]=@user_id",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_name",DbType.String,oUser.user_name));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_tel",DbType.String,oUser.user_tel));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_mail",DbType.String,oUser.user_mail));
				if (oUser.user_role.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_role",DbType.Int32,oUser.user_role));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_role",DbType.Int32,DBNull.Value));
				if (oUser.user_lvl_notif.HasValue)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_lvl_notif",DbType.Int32,oUser.user_lvl_notif));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_lvl_notif",DbType.Int32,DBNull.Value));
				if (oUser.user_pwd!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_pwd",DbType.String,oUser.user_pwd));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_pwd",DbType.String,DBNull.Value));
				if (oUser.user_login!=null)
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_login",DbType.String,oUser.user_login));
				else
					oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_login",DbType.String,DBNull.Value));
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_id",DbType.Int32,oUser.user_id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveUser(User oUser)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETE FROM [User] WHERE [user_id]=@user_id",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_id",DbType.Int32,oUser.user_id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
		public int RemoveUser(int user_id)
		{

			try
			{
				DbCommand oDbCommand = DbProviderHelper.CreateCommand("DELETE FROM [User] WHERE [user_id]=@user_id",CommandType.Text);
				oDbCommand.Parameters.Add(DbProviderHelper.CreateParameter("@user_id",DbType.Int32,user_id));
				return DbProviderHelper.ExecuteNonQuery(oDbCommand);
			}
			catch (Exception ex)
			{
				throw ex;
			}
		}
	}
}
